var express = require('express');
var conn = require('../public/javascripts/connect');
var ptt = require('../public/toFixed/toFixed');
var calc = require('../public/Backstage/Calculation');
var calcZB = require('../public/Backstage/ZBCalculationSummary');//2级巴装备的计算
let FZCAL = require('../public/TwoGradeBar/FZCAL');//2级巴发展的计算
let ZXCAL = require('../public/TwoGradeBar/ZXCAL');//2级巴中心的计算
let JSWLCAL = require('../public/OneGradeBar/JSWLCAL');//1级巴金石物流的计算let
var router = express.Router();
/* GET home page. */
router.get('/', async function (req, res, next) {
    let Year, Month, tableArr = [], user = {};
    let daparentName = decodeURIComponent(req.query.tableName.split(",")[0]);
    let sqlState = `SELECT state FROM correspondence_table WHERE table_name='${daparentName}'`;

    let state1 = await new Promise(resP => {
        conn.connection2.query(sqlState, function (err, result, fields) {
            if (err) {
                console.log('[SELECT ERROR] - ', err.message);
                return;
            }
            console.log("result", result)
            resP(result)
        });
    })
    console.log("state", state1[0].state);
    if (state1[0].state === 2) {
        res.json({message: "请联系管理员解锁后修改"});
        return;
    }
    // 物流装备事业部_2018_9
    user.dpN = daparentName.split("_")[0];//
    user.Year = daparentName.split("_")[1];//年份
    user.Month = Number(daparentName.split("_")[2]);//月份
    user.Gread = decodeURIComponent(req.query.tableName.split(",")[1]);//等级
    if (user.Gread == 1) {
        user.TitleBar = `金山物流${user.Year}年${user.Month}月份${user.Gread}级巴经营会计损益表`;
    } else if (user.Gread == 2) {
        user.TitleBar = `${user.dpN}${user.Year}年${user.Month}月份${user.Gread}级巴经营会计损益表(${user.dpN})`;
    } else if (user.Gread == 3) {
        user.TitleBar = `${user.dpN}${user.Year}年${user.Month}月份${user.Gread}级巴经营会计损益表`;
    }
    console.log(daparentName)
    //查询语句
    let sql = `SELECT * FROM ${daparentName} ORDER BY 行号 ASC`;
    let sqlZiduan = `SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = '${daparentName}'`;

    await new Promise(resP => {
        conn.connection2.query(sqlZiduan, function (err, result, fields) {
            if (err) {
                console.log('[SELECT ERROR] - ', err.message);
                return;
            }
            console.log("result", result)
            user.data_field = result;
            resP(true)
        });
    })

    console.log(sql);
    conn.connection2.query(sql, function (err, result, fields) {
        if (err) {
            console.log('[SELECT ERROR] - ', err.message);
            return;
        }
        for (let i in result) {
            for (let j in result[i]) {
                if (j === "id" || j === "行号" || j === "科目" || j === "年份" || j === "月份") {
                    continue;
                } else if (j === "去年同期比率" || j === "去年同期累计比率" || j === "年度计划金额比率" || j === "当月累计比率" || j === "当月累计完成率" || j === "累比增减率" || j === "上月比率" || j === "当月计划比率" || j === "当月比率值" || j === "当月实际完成率" || j === "环比增降幅" || j === "同比增降幅" || j === "计划增降幅" || j === "下月比率值") {
                    result[i][j] = ptt.calculation1.toFixed((Number(result[i][j]) * 100), 2) + "%";
                    continue;
                }
                if (typeof result[i][j] === 'number') {
                    result[i][j] = ptt.calculation1.toFixed(result[i][j], 2)
                }
                if (j.indexOf("修改日期") != -1) {
                    if (null == result[i][j] || "undefined" == typeof(result[i][j]) || "" == result[i][j]) {
                    } else {
                        result[i][j] = result[i][j].toLocaleDateString();
                    }
                }
            }
        }
        user.data_result = result;
        res.render('editortable', user);
    });
});
router.post("/", async function (req, res, next) {
    let {lineNumber, field, valnum, state, tableName} = req.body;
    //一键锁定功能begin
    if (state) {
        let sql1 = `UPDATE correspondence_table SET state=${state} WHERE table_name='${tableName}'`;
        conn.connection2.query(sql1, function (err, result, filed) {
            if (err) {
                return '[UPDATE ERROR]::' + err;
            }
            console.log("result", result)
            res.json({saveSuc: true})
        })
        return;
    }
    //一键锁定功能end

    let sql = `UPDATE ${tableName} SET ${field}=${valnum},修改日期=now() WHERE 行号=${lineNumber}`;
    await new Promise(res => {
        conn.connection2.query(sql, function (err, result, filed) {
            if (err) {
                return '[UPDATE ERROR]::' + err;
            }
            console.log("result", result);
            res(true);
        })
    })
    if (tableName.indexOf('物流装备事业部') != -1) {
        await calcZB.calculation1.cal(tableName);//2级巴装备计算
    } else if (tableName.indexOf('经营管理中心') != -1) {
        await ZXCAL.calculation1.cal(tableName);//2级巴经营管理中心计算
    } else if (tableName.indexOf('物流发展事业部') != -1) {
        await FZCAL.calculation1.cal(tableName);//2级巴发展的计算
    } else if (tableName.indexOf('金石物流') != -1) {
        await JSWLCAL.calculation1.cal(tableName);//1级巴金石物流计算

    } else {
        await calc.calculation1.cal(tableName);//执行计算
    }
    let sqldata = `SELECT * FROM ${tableName} ORDER BY 行号 ASC`;
    conn.connection2.query(sqldata, function (err, result, fields) {
        if (err) {
            console.log('[SELECT ERROR] - ', err.message);
            return;
        }
        for (let i in result) {
            for (let j in result[i]) {
                if (j === "id" || j === "行号" || j === "科目" || j === "年份" || j === "月份") {
                    continue;
                } else if (j === "去年同期比率" || j === "去年同期累计比率" || j === "年度计划金额比率" || j === "当月累计比率" || j === "当月累计完成率" || j === "累比增减率" || j === "上月比率" || j === "当月计划比率" || j === "当月比率值" || j === "当月实际完成率" || j === "环比增降幅" || j === "同比增降幅" || j === "计划增降幅" || j === "下月比率值") {
                    result[i][j] = ptt.calculation1.toFixed((Number(result[i][j]) * 100), 2) + "%";
                    continue;
                }
                if (typeof result[i][j] === 'number') {
                    result[i][j] = ptt.calculation1.toFixed(result[i][j], 2)
                }
                if (j.indexOf("修改日期") != -1) {
                    if (null == result[i][j] || "undefined" == typeof(result[i][j]) || "" == result[i][j]) {
                    } else {
                        result[i][j] = result[i][j].toLocaleDateString();
                    }
                }
            }
        }
        // user.data_result = result;
        res.json({Modification: true, result})

        // res.render('editortable', user);
    });
    // res.json({Modification: true})


});
router.post("/reserveddecimal", async function (req, res, next) {
    let {decimaldigit, tableName} = req.body;
    let sqldata = `SELECT * FROM ${tableName} ORDER BY 行号 ASC`;
    conn.connection2.query(sqldata, function (err, result, fields) {
        if (err) {
            console.log('[SELECT ERROR] - ', err.message);
            return;
        }
        for (let i in result) {
            for (let j in result[i]) {
                if (j === "id" || j === "行号" || j === "科目" || j === "年份" || j === "月份") {
                    continue;
                } else if (j === "去年同期比率" || j === "去年同期累计比率" || j === "年度计划金额比率" || j === "当月累计比率" || j === "当月累计完成率" || j === "累比增减率" || j === "上月比率" || j === "当月计划比率" || j === "当月比率值" || j === "当月实际完成率" || j === "环比增降幅" || j === "同比增降幅" || j === "计划增降幅" || j === "下月比率值") {
                    result[i][j] = ptt.calculation1.toFixed((Number(result[i][j]) * 100), 2) + "%";
                    continue;
                }
                if (typeof result[i][j] === 'number') {
                    result[i][j] = ptt.calculation1.toFixed(result[i][j], decimaldigit)
                }
                if (j.indexOf("修改日期") != -1) {
                    if (null == result[i][j] || "undefined" == typeof(result[i][j]) || "" == result[i][j]) {
                    } else {
                        result[i][j] = result[i][j].toLocaleDateString();
                    }
                }
            }
        }
        res.json({Modification: true, result})
    });


});
//查看底层数据begin
router.post("/ViDaSou", async function (req, res, next) {
    let {lineNumber, field, tableName} = req.body;
    let [tablename1, year, month] = tableName.split("_");
    let sql = `SELECT grade,deparentNum FROM correspondence_table WHERE table_name='${tableName}'`;
    let resuls = await new Promise((resP, req) => {
        conn.connection2.query(sql, function (err, result, fileds) {
            if (err) {
                console.log("[查找错误]::", err);
                return;
                res(false)
            }
            resP(result)
        })
    })
    grade = resuls[0].grade;
    let deparentNum = resuls[0].deparentNum;
    if (grade === 3) {
        res.json({"BasicData": "当前数据已经是最底层抓取数据"});
        return;
    } else if (grade === 2) {
        let aql1 = `SELECT deparentNum,departmentName FROM organization WHERE parentdepartment_id=${deparentNum}`
        let resuls1 = await new Promise((resP, req) => {
            conn.connection1.query(aql1, function (err, result, fileds) {
                if (err) {
                    console.log("[查找错误]::", err);
                    return;
                    res(false)
                }
                resP(result)
            })
        })
        console.log("resuls1", resuls1);
        let table_name = null, BasicData = [], sqlBase = null;
        for (let i in resuls1) {
            await (async function (i) {
                table_name = `${resuls1[i].departmentName}_${year}_${month}`;
                console.log(i, table_name);
                sqlBase = `CALL judgexists('${table_name}','${field}',${lineNumber},@sumary)`;
                // sqlBase = `SELECT ${field} FROM ${table_name} WHERE 行号=${lineNumber}`;
                console.log('sqlBase', sqlBase);
                await new Promise((resP, rej) => {
                    conn.connection2.query(sqlBase, function (err, result, fileds) {
                        if (err) {
                            console.log("[基础数据查找错误]", err);
                            resP(false)
                            return;
                        }
                        result[0] = JSON.stringify(result[0]);
                        result[0] = JSON.parse(result[0])[0];
                        result[0].table_name = table_name;
                        BasicData.push(result[0]);
                        resP(true);
                    })
                })
            })(i)
        }
        console.log("BasicData", BasicData);
        if (BasicData === undefined || BasicData.length == 0) {
            res.json({BasicData: "当前数据已经是最底层抓取数据"});
        } else {
            res.json({BasicData});
        }
        return;
    } else if (grade === 1) {
        let aql1 = `SELECT deparentNum,departmentName FROM organization WHERE parentdepartment_id=${deparentNum}`
        let resuls1 = await new Promise((resP, req) => {
            conn.connection1.query(aql1, function (err, result, fileds) {
                if (err) {
                    console.log("[查找错误]::", err);
                    return;
                    res(false)
                }
                resP(result)
            })
        })
        console.log("resuls1", resuls1);
        let table_name = null, BasicData = [], sqlBase = null;
        for (let i in resuls1) {
            await (async function (i) {
                table_name = `${resuls1[i].departmentName}_${year}_${month}`;
                console.log(i, table_name)

                sqlBase = `CALL judgexists('${table_name}','${field}',${lineNumber},@sumary)`;
                // sqlBase = `SELECT ${field} FROM ${table_name} WHERE 行号=${lineNumber}`;
                console.log('sqlBase', sqlBase)
                await new Promise((resP, rej) => {
                    conn.connection2.query(sqlBase, function (err, result, fileds) {
                        if (err) {
                            console.log("[基础数据查找错误]", err);
                            resP(false)
                            return;
                        }
                        result[0] = JSON.stringify(result[0]);
                        result[0] = JSON.parse(result[0])[0];
                        result[0].table_name = table_name;
                        BasicData.push(result[0]);
                        resP(true)
                    })
                })
            })(i)
        }
        console.log("BasicData", BasicData);
        if (BasicData === undefined || BasicData.length == 0) {
            res.json({BasicData: "当前数据已经是最底层抓取数据"})
        } else {
            res.json({BasicData})
        }
        return;

    }
    console.log("lineNumber", lineNumber)
    console.log("field", field)
    console.log("tableName", tableName)
    res.json({saveSuc: true})

});
//查看底层数据end
module.exports = router;